﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;


namespace Chenxin.Lib.Passport.DAL
{
    /// <summary>
    /// 数据访问类System_SerialNumber。
    /// </summary>
    public class System_SerialNumber
    {
        public System_SerialNumber()
        { }
        #region  成员方法

        /// <summary>
        /// 根据MAC地址，判断记录是否存在，且状态为激活状态
        /// </summary>
        /// <param name="mac">Mac地址</param>
        /// <returns></returns>
        public static bool CheckExist(string mac)
        {
            DataSet ds = GetList(string.Format(" 1=1 and MACAddress='{0}' and Status=1", mac));
            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count == 1)
                {
                    return true;
                }
            }
            return false;
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public static void Add(Chenxin.Lib.Passport.Entity.System_SerialNumber model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into System_SerialNumber(");
            strSql.Append("SerialNumber,MACAddress,CityId,City,CompanyId,Company,Depart,UserName,ConstractPhone,Addtime,OprPersonId,OprPerson,OprCityId,OprCity,OprCompanyId,OprCompany,OprDepartId,OprDepart,Status)");

            strSql.Append(" values (");
            strSql.Append("@SerialNumber,@MACAddress,@CityId,@City,@CompanyId,@Company,@Depart,@UserName,@ConstractPhone,@Addtime,@OprPersonId,@OprPerson,@OprCityId,@OprCity,@OprCompanyId,@OprCompany,@OprDepartId,@OprDepart,@Status)");
            Database db = DatabaseFactory.CreateDatabase("Chenxin_Main");
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "SerialNumber", DbType.Guid, model.SerialNumber);
            db.AddInParameter(dbCommand, "MACAddress", DbType.AnsiString, model.MACAddress);
            db.AddInParameter(dbCommand, "CityId", DbType.Guid, model.CityId);
            db.AddInParameter(dbCommand, "City", DbType.AnsiString, model.City);
            db.AddInParameter(dbCommand, "CompanyId", DbType.Guid, model.CompanyId);
            db.AddInParameter(dbCommand, "Company", DbType.AnsiString, model.Company);
            db.AddInParameter(dbCommand, "Depart", DbType.AnsiString, model.Depart);
            db.AddInParameter(dbCommand, "UserName", DbType.AnsiString, model.UserName);
            db.AddInParameter(dbCommand, "ConstractPhone", DbType.AnsiString, model.ConstractPhone);
            db.AddInParameter(dbCommand, "Addtime", DbType.DateTime, model.Addtime);
            db.AddInParameter(dbCommand, "OprPersonId", DbType.Guid, model.OprPersonId);
            db.AddInParameter(dbCommand, "OprPerson", DbType.AnsiString, model.OprPerson);
            db.AddInParameter(dbCommand, "OprCityId", DbType.Guid, model.OprCityId);
            db.AddInParameter(dbCommand, "OprCity", DbType.AnsiString, model.OprCity);
            db.AddInParameter(dbCommand, "OprCompanyId", DbType.Guid, model.OprCompanyId);
            db.AddInParameter(dbCommand, "OprCompany", DbType.AnsiString, model.OprCompany);
            db.AddInParameter(dbCommand, "OprDepartId", DbType.Guid, model.OprDepartId);
            db.AddInParameter(dbCommand, "OprDepart", DbType.AnsiString, model.OprDepart);
            db.AddInParameter(dbCommand, "Status", DbType.Int32, model.Status);
            db.ExecuteNonQuery(dbCommand);
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public static void Update(Chenxin.Lib.Passport.Entity.System_SerialNumber model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update System_SerialNumber set ");
            strSql.Append("MACAddress=@MACAddress,");
            strSql.Append("CityId=@CityId,");
            strSql.Append("City=@City,");
            strSql.Append("CompanyId=@CompanyId,");
            strSql.Append("Company=@Company,");
            strSql.Append("Depart=@Depart,");
            strSql.Append("UserName=@UserName,");
            strSql.Append("ConstractPhone=@ConstractPhone,");
            strSql.Append("Addtime=@Addtime,");
            strSql.Append("OprPersonId=@OprPersonId,");
            strSql.Append("OprPerson=@OprPerson,");
            strSql.Append("OprCityId=@OprCityId,");
            strSql.Append("OprCity=@OprCity,");
            strSql.Append("OprCompanyId=@OprCompanyId,");
            strSql.Append("OprCompany=@OprCompany,");
            strSql.Append("OprDepartId=@OprDepartId,");
            strSql.Append("OprDepart=@OprDepart,");
            strSql.Append("Status=@Status");
            strSql.Append(" where SerialNumber=@SerialNumber ");
            Database db = DatabaseFactory.CreateDatabase("Chenxin_Main");
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "SerialNumber", DbType.Guid, model.SerialNumber);
            db.AddInParameter(dbCommand, "MACAddress", DbType.AnsiString, model.MACAddress);
            db.AddInParameter(dbCommand, "CityId", DbType.Guid, model.CityId);
            db.AddInParameter(dbCommand, "City", DbType.AnsiString, model.City);
            db.AddInParameter(dbCommand, "CompanyId", DbType.Guid, model.CompanyId);
            db.AddInParameter(dbCommand, "Company", DbType.AnsiString, model.Company);
            db.AddInParameter(dbCommand, "Depart", DbType.AnsiString, model.Depart);
            db.AddInParameter(dbCommand, "UserName", DbType.AnsiString, model.UserName);
            db.AddInParameter(dbCommand, "ConstractPhone", DbType.AnsiString, model.ConstractPhone);
            db.AddInParameter(dbCommand, "Addtime", DbType.DateTime, model.Addtime);
            db.AddInParameter(dbCommand, "OprPersonId", DbType.Guid, model.OprPersonId);
            db.AddInParameter(dbCommand, "OprPerson", DbType.AnsiString, model.OprPerson);
            db.AddInParameter(dbCommand, "OprCityId", DbType.Guid, model.OprCityId);
            db.AddInParameter(dbCommand, "OprCity", DbType.AnsiString, model.OprCity);
            db.AddInParameter(dbCommand, "OprCompanyId", DbType.Guid, model.OprCompanyId);
            db.AddInParameter(dbCommand, "OprCompany", DbType.AnsiString, model.OprCompany);
            db.AddInParameter(dbCommand, "OprDepartId", DbType.Guid, model.OprDepartId);
            db.AddInParameter(dbCommand, "OprDepart", DbType.AnsiString, model.OprDepart);
            db.AddInParameter(dbCommand, "Status", DbType.Int32, model.Status);
            db.ExecuteNonQuery(dbCommand);

        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public static void Delete(Guid SerialNumber)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from System_SerialNumber ");
            strSql.Append(" where SerialNumber=@SerialNumber ");
            Database db = DatabaseFactory.CreateDatabase("Chenxin_Main");
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "SerialNumber", DbType.Guid, SerialNumber);
            db.ExecuteNonQuery(dbCommand);
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public static Chenxin.Lib.Passport.Entity.System_SerialNumber GetModel(Guid SerialNumber)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select SerialNumber,MACAddress,CityId,City,CompanyId,Company,Depart,UserName,ConstractPhone,Addtime,OprPersonId,OprPerson,OprCityId,OprCity,OprCompanyId,OprCompany,OprDepartId,OprDepart,Status from System_SerialNumber ");
            strSql.Append(" where SerialNumber=@SerialNumber ");
            Database db = DatabaseFactory.CreateDatabase("Chenxin_Main_Search");
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "SerialNumber", DbType.Guid, SerialNumber);
            Chenxin.Lib.Passport.Entity.System_SerialNumber model = null;
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                if (dataReader.Read())
                {
                    model = ReaderBind(dataReader);
                }
                dataReader.Close();
                dataReader.Dispose();
            }
            return model;
        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public static DataSet GetListBySql(string strSQL)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(strSQL);
            Database db = DatabaseFactory.CreateDatabase("Chenxin_Main_Search");
            return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public static DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select SerialNumber,MACAddress,CityId,City,CompanyId,Company,Depart,UserName,ConstractPhone,Addtime,OprPersonId,OprPerson,OprCityId,OprCity,OprCompanyId,OprCompany,OprDepartId,OprDepart,Status ");
            strSql.Append(" FROM System_SerialNumber ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            Database db = DatabaseFactory.CreateDatabase("Chenxin_Main_Search");
            return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }

        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public static DataSet GetList(int PageIndex, int PageSize, String OrderField, string TableName, string StrWhere, string FieldList, string TableFieldList, bool DoCount)
        {
            Database db = DatabaseFactory.CreateDatabase("Chenxin_Main_Search");
            DbCommand dbCommand = db.GetStoredProcCommand("PagerMultiTable");
            db.AddInParameter(dbCommand, "PageIndex", DbType.Int32, PageIndex);
            db.AddInParameter(dbCommand, "PageSize", DbType.Int32, PageSize);
            db.AddInParameter(dbCommand, "OrderField", DbType.AnsiString, OrderField);
            db.AddInParameter(dbCommand, "TableName", DbType.AnsiString, TableName);
            db.AddInParameter(dbCommand, "StrWhere", DbType.AnsiString, StrWhere);
            db.AddInParameter(dbCommand, "FieldList", DbType.AnsiString, FieldList);
            db.AddInParameter(dbCommand, "TableFieldList", DbType.AnsiString, TableFieldList);
            db.AddInParameter(dbCommand, "DoCount", DbType.Boolean, DoCount);
            return db.ExecuteDataSet(dbCommand);
        }


        //直接执行SQL语句
        public static bool ExecuteSQL(string strSQL)
        {
            Database db = DatabaseFactory.CreateDatabase("Chenxin_Main_Search");
            int count = db.ExecuteNonQuery(CommandType.Text, strSQL);
            if (count > 0)
                return true;
            else
                return false;
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("UP_GetRecordByPage");
            db.AddInParameter(dbCommand, "tblName", DbType.AnsiString, "System_SerialNumber");
            db.AddInParameter(dbCommand, "fldName", DbType.AnsiString, "ID");
            db.AddInParameter(dbCommand, "PageSize", DbType.Int32, PageSize);
            db.AddInParameter(dbCommand, "PageIndex", DbType.Int32, PageIndex);
            db.AddInParameter(dbCommand, "IsReCount", DbType.Boolean, 0);
            db.AddInParameter(dbCommand, "OrderType", DbType.Boolean, 0);
            db.AddInParameter(dbCommand, "strWhere", DbType.AnsiString, strWhere);
            return db.ExecuteDataSet(dbCommand);
        }*/

        /// <summary>
        /// 获得数据列表（比DataSet效率高，推荐使用）
        /// </summary>
        public static List<Chenxin.Lib.Passport.Entity.System_SerialNumber> GetListArray(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select SerialNumber,MACAddress,CityId,City,CompanyId,Company,Depart,UserName,ConstractPhone,Addtime,OprPersonId,OprPerson,OprCityId,OprCity,OprCompanyId,OprCompany,OprDepartId,OprDepart,Status ");
            strSql.Append(" FROM System_SerialNumber ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            List<Chenxin.Lib.Passport.Entity.System_SerialNumber> list = new List<Chenxin.Lib.Passport.Entity.System_SerialNumber>();
            Database db = DatabaseFactory.CreateDatabase("Chenxin_Main_Search");
            using (IDataReader dataReader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (dataReader.Read())
                {
                    list.Add(ReaderBind(dataReader));
                }
                dataReader.Close();
                dataReader.Dispose();
            }
            return list;
        }




        /// <summary>
        /// 对象实体绑定数据
        /// </summary>
        public static Chenxin.Lib.Passport.Entity.System_SerialNumber ReaderBind(IDataReader dataReader)
        {
            Chenxin.Lib.Passport.Entity.System_SerialNumber model = new Chenxin.Lib.Passport.Entity.System_SerialNumber();
            object ojb;
            ojb = dataReader["SerialNumber"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.SerialNumber = new Guid(ojb.ToString());
            }
            model.MACAddress = dataReader["MACAddress"].ToString();
            ojb = dataReader["CityId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.CityId = new Guid(ojb.ToString());
            }
            model.City = dataReader["City"].ToString();
            ojb = dataReader["CompanyId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.CompanyId = new Guid(ojb.ToString());
            }
            model.Company = dataReader["Company"].ToString();
            model.Depart = dataReader["Depart"].ToString();
            model.UserName = dataReader["UserName"].ToString();
            model.ConstractPhone = dataReader["ConstractPhone"].ToString();
            ojb = dataReader["Addtime"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.Addtime = (DateTime)ojb;
            }
            ojb = dataReader["OprPersonId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.OprPersonId = new Guid(ojb.ToString());
            }
            model.OprPerson = dataReader["OprPerson"].ToString();
            ojb = dataReader["OprCityId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.OprCityId = new Guid(ojb.ToString());
            }
            model.OprCity = dataReader["OprCity"].ToString();
            ojb = dataReader["OprCompanyId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.OprCompanyId = new Guid(ojb.ToString());
            }
            model.OprCompany = dataReader["OprCompany"].ToString();
            ojb = dataReader["OprDepartId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.OprDepartId = new Guid(ojb.ToString());
            }
            model.OprDepart = dataReader["OprDepart"].ToString();
            ojb = dataReader["Status"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.Status = (int)ojb;
            }
            return model;
        }

        #endregion  成员方法
    }

}
